package org.eng2.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class FeedbackPratosDAO {
	
	protected ArrayList<FeedbackPratos> getAll() throws SQLException {
		PreparedStatement ps;
		FeedbackPratos fb = null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("select avg(avaliacao) avaliacao, item.titulo prato, count(*) num_votos ");
		sql.append("from feedback fb ");
		sql.append("inner join item on fb.item_id = item.id ");
		sql.append("inner join categoria ctg on ctg.id = item.categoria_id ");
		sql.append("where ctg.id = 2 ");
		sql.append("group by item.titulo");
		
		ArrayList<FeedbackPratos> feedbackList = null;
		
		try {
			ps = DataBase.getConnectionDB().
					prepareStatement(sql.toString());
						
			ResultSet rs = ps.executeQuery();
			feedbackList = new ArrayList<FeedbackPratos>();
			
			while (rs.next()) {
				fb = new FeedbackPratos();
				fb.setVotos(rs.getInt("num_votos"));
				fb.setAvaliacao(rs.getFloat("avaliacao"));
				fb.setPrato(rs.getString("prato"));
				feedbackList.add(fb);
			}
		} catch (Exception e) {}
		
		return feedbackList;
	}
}
